﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Dictionary.Core
{
    public class Category
    {
        public int id { get; set; }
        public int source_id { get; set; }
        public String title { get; set; }
        public String link { get; set; }
        public String regular_exp { get; set; }
        public String created { get; set; }
        public String updated { get; set; }
        public int status { get; set; }
        /// <summary>
        /// Default constructor with no parameter
        /// </summary>
        public Category()
        { 
        }
        /// <summary>
        /// Constructor with Title and Link
        /// </summary>
        /// <param name="title">String</param>
        /// <param name="link">String</param>
        public Category(String title, String link)
        {
            this.title = title;
            this.link = link;
        }
        /// <summary>
        /// Constructor with Title, Link and Regular Exp
        /// </summary>
        /// <param name="title">String</param>
        /// <param name="link">String</param>
        /// <param name="regular_exp">String</param>
        public Category(String title, String link, String regular_exp)
        {
            this.title = title;
            this.link = link;
            this.regular_exp = regular_exp;
        }
        
        /// <summary>
        /// Method check Url category is exist in Database
        /// </summary>
        /// <returns>bool</returns>
        public bool isExist()
        {
            bool isExist = false;
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM category WHERE link = \"" + this.link + "\"";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if(dataReader.HasRows)
                    isExist = true;
                else
                    isExist = false;
                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return isExist;
        }
        /// <summary>
        /// Method to select category form database
        /// </summary>
        public void select()
        {
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM category WHERE id = " + this.id;
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    this.id = Convert.ToInt16(dataReader["id"]);
                    this.source_id = Convert.ToInt16(dataReader["source_id"]);
                    this.title = dataReader["title"].ToString();
                    this.link = dataReader["link"].ToString();
                    this.regular_exp = dataReader["regular_exp"].ToString();
                    this.created = dataReader["created"].ToString();
                    this.updated = dataReader["updated"].ToString();
                    this.status = Convert.ToInt16(dataReader["status"]);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
        }
        
        /// <summary>
        /// Method add new category to database
        /// </summary>
        /// <returns>bool</returns>
        public bool insert()
        {
            if (!this.isExist())
            {
                SQLHelper sqlHelper = new SQLHelper();
                String strInsert = "INSERT INTO category(source_id,title,link,created,updated) " +
                                   "VALUES(" + this.source_id + ",\"" + this.title + "\",\"" + this.link + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\")";
                if (sqlHelper.executeNonQuery(strInsert) > 0)
                {
                    String strQuery = "SELECT * FROM category WHERE link = '" + this.link + "'";
                    //Create Command
                    MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                    //Create a data reader and Execute the command
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    int row = 0;
                    while (dataReader.Read())
                    {
                        this.id = Convert.ToInt16(dataReader["id"]);
                        this.source_id = Convert.ToInt16(dataReader["source_id"]);
                        this.title = dataReader["title"].ToString();
                        this.link = dataReader["link"].ToString();
                        this.regular_exp = dataReader["regular_exp"].ToString();
                        this.created = dataReader["created"].ToString();
                        this.updated = dataReader["updated"].ToString();
                        this.status = Convert.ToInt16(dataReader["status"]);
                        row++;
                    }
                    return true;
                }
                else
                    return false;
            }
            return false;
        }
        
        /// <summary>
        /// Method to reget Vocab inside category
        /// </summary>
        /// <returns></returns>
        public List<Vocabulary> regetVocab()
        {
            Crawler crawler = new Crawler(this.link);
            List<Vocabulary> listVocab = crawler.getVocabulary();
            // delete all old vocal
            Vocabulary.deleteAll(this.id);
            // save vocab
            Vocabulary.insertAll(listVocab, this.id);
            return Vocabulary.selectAll(this.id);
        }
        /// <summary>
        /// Method to update category
        /// </summary>
        /// <returns>bool</returns>
        public bool update()
        {
            if (!this.isExist())
            {
                SQLHelper sqlHelper = new SQLHelper();
                String strInsert = "UPDATE category SET " +
                                    "title = \"" + this.title + "\", " +
                                    "link= \"" + this.link + "\", " +
                                    "regular_exp= \"" + this.regular_exp + "\", " +
                                    "updated=\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\" " +
                                    "WHERE id = " + this.id;
                if (sqlHelper.executeNonQuery(strInsert) > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            return false;
        }
        /// <summary>
        /// Method to delete category
        /// </summary>
        /// <returns>bool</returns>
        public bool delete()
        {
            if (Vocabulary.deleteAll(this.id))
            {
                SQLHelper sqlHelper = new SQLHelper();
                String strDelete = "DELETE FROM category WHERE id=" + this.id;
                if (sqlHelper.executeNonQuery(strDelete) > 0)
                    return true;
                else
                    return false;
            }
            else
                return false;
        }

        /// <summary>
        /// Metho to convert objet to String
        /// </summary>
        /// <returns></returns>
        public String toString()
        {
            return this.source_id + " : " + this.title + " - " + this.link;
        }

        /// <summary>
        /// Method to select all category in source form databse
        /// </summary>
        /// <param name="source_id">int</param>
        /// <returns>List<Category></returns>
        public static List<Category> selectAll(int source_id)
        {
            List<Category> listResult = new List<Category>();
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM category WHERE source_id=" + source_id;
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                int row = 0;
                while (dataReader.Read())
                {
                    Category temp = new Category();
                    temp.id = Convert.ToInt16(dataReader["id"]);
                    temp.source_id = Convert.ToInt16(dataReader["source_id"]);
                    temp.title = dataReader["title"].ToString();
                    temp.link = dataReader["link"].ToString();
                    temp.regular_exp = dataReader["regular_exp"].ToString();
                    temp.created = dataReader["created"].ToString();
                    temp.updated = dataReader["updated"].ToString();
                    temp.status = Convert.ToInt16(dataReader["status"]);
                    row++;
                    listResult.Add(temp);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return listResult;
        }

        /// <summary>
        /// Method to search Category by Name
        /// </summary>
        /// <param name="source_id">int</param>
        /// <param name="categoryName">String</param>
        /// <returns>List<Category></returns>
        public static List<Category> search(int source_id, String strSearch)
        {
            strSearch = strSearch.Replace("\"", "");
            List<Category> listResult = new List<Category>();
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM category WHERE source_id=" + source_id +
                              " AND (title LIKE \"%" + strSearch + "%\" OR link LIKE \"%" + strSearch + "%\")";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                int row = 0;
                while (dataReader.Read())
                {
                    Category temp = new Category();
                    temp.id = Convert.ToInt16(dataReader["id"]);
                    temp.source_id = Convert.ToInt16(dataReader["source_id"]);
                    temp.title = dataReader["title"].ToString();
                    temp.link = dataReader["link"].ToString();
                    temp.regular_exp = dataReader["regular_exp"].ToString();
                    temp.created = dataReader["created"].ToString();
                    temp.updated = dataReader["updated"].ToString();
                    temp.status = Convert.ToInt16(dataReader["status"]);
                    row++;
                    listResult.Add(temp);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return listResult;
        }
        
        /// <summary>
        /// Method to insert list Category
        /// </summary>
        /// <param name="listCategory"></param>
        /// <returns></returns>
        public static int insertAll(List<Category> listCategory, int source_id)
        {
            int count = 0;
            SQLHelper sqlHelper = new SQLHelper();
            MySqlDataReader dataReader;
            if (sqlHelper.OpenConnection())
            {
                MySqlCommand cmd;
                foreach (Category itemCategory in listCategory)
                {
                    // to check exist in database
                    String strQuery = "SELECT * FROM category WHERE link = \"" + itemCategory.link + "\"";
                    cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                    dataReader = cmd.ExecuteReader();
                    if (!dataReader.HasRows)
                    {
                        dataReader.Close();
                        // to save to database
                        String strInsert = "INSERT INTO category(source_id,title,link,created,updated) " +
                                           "VALUES(" + source_id + ",\"" + itemCategory.title + "\",\"" + itemCategory.link + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\")";
                        cmd = new MySqlCommand(strInsert, sqlHelper.getConnection());
                        if (cmd.ExecuteNonQuery() > 0)
                        {
                            count++;
                        }
                    }
                    else
                        dataReader.Close();
                }
            }
            sqlHelper.CloseConnection();
            return count;
        }
        /// <summary>
        /// Method to get count category in database
        /// </summary>
        /// <param name="source_id"></param>
        /// <returns></returns>
        public static int count()
        {
            int count = 0;
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT COUNT(*) AS 'count' FROM category";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    count = Convert.ToInt16(dataReader["count"]);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return count;
        }
    }
}
